package com.live.shop.sql;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.live.shop.model.PrologueModel;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class PrologueListDatabase {
	private static MyDatabaseHelper helper;
	public PrologueListDatabase(Context context){
		//创建数据库和表
		helper = new MyDatabaseHelper(context, "user.db",null,3,null);
	}
	@SuppressLint("Range")
	public static List<PrologueModel> selectAll() {
		List<PrologueModel> list = new ArrayList<>();
		SQLiteDatabase database = helper.getReadableDatabase();
		Cursor cursor = database.rawQuery("select * from prologue_list ", null);
		if (cursor != null && cursor.getCount() > 0) {
			while (cursor.moveToNext()) {
				PrologueModel prologueModel = new PrologueModel();
				prologueModel.setPrologueId(cursor.getInt(cursor.getColumnIndex("prologue_id")));
				prologueModel.setPrologueTxt(cursor.getString(cursor.getColumnIndex("prologue_txt")));
				prologueModel.setPrologueType(cursor.getInt(cursor.getColumnIndex("prologue_type")));
				prologueModel.setPrologueState(cursor.getInt(cursor.getColumnIndex("prologue_state")));
				prologueModel.setPrologueAudio(cursor.getString(cursor.getColumnIndex("prologue_audio")));
				prologueModel.setPrologueStatus(cursor.getInt(cursor.getColumnIndex("prologue_status")));
				prologueModel.setUseNum(cursor.getInt(cursor.getColumnIndex("use_num")));
				prologueModel.setCreateTime(cursor.getString(cursor.getColumnIndex("create_time")));
				list.add(prologueModel);
			}
			cursor.close();
		}
		database.close();
		return list;
	}
	@SuppressLint("Range")
	public static List<PrologueModel> selectAllStatusYes() {
		List<PrologueModel> list = new ArrayList<>();
		SQLiteDatabase database = helper.getReadableDatabase();
		Cursor cursor = database.rawQuery("select * from prologue_list where prologue_status=1", null);
		if (cursor != null && cursor.getCount() > 0) {
			while (cursor.moveToNext()) {
				PrologueModel prologueModel = new PrologueModel();
				prologueModel.setPrologueId(cursor.getInt(cursor.getColumnIndex("prologue_id")));
				prologueModel.setPrologueTxt(cursor.getString(cursor.getColumnIndex("prologue_txt")));
				prologueModel.setPrologueType(cursor.getInt(cursor.getColumnIndex("prologue_type")));
				prologueModel.setPrologueState(cursor.getInt(cursor.getColumnIndex("prologue_state")));
				prologueModel.setPrologueAudio(cursor.getString(cursor.getColumnIndex("prologue_audio")));
				prologueModel.setPrologueStatus(cursor.getInt(cursor.getColumnIndex("prologue_status")));
				prologueModel.setUseNum(0);
				prologueModel.setCreateTime(cursor.getString(cursor.getColumnIndex("create_time")));
				list.add(prologueModel);
			}
			cursor.close();
		}
		database.close();
		return list;
	}
	//查询本地当前数量
	@SuppressLint("Range")
	public static String[] selectNum() {
		String[] result = new String[2];
		SQLiteDatabase database = helper.getReadableDatabase();
		Cursor cursor = database.rawQuery("SELECT COUNT(prologue_id) AS prologue_num,SUM(use_num) AS use_num FROM prologue_list", null);
		if (cursor != null && cursor.getCount() > 0) {
			if (cursor.moveToNext()) {
				result[0] = String.valueOf(cursor.getInt(cursor.getColumnIndex("prologue_num")));
				result[1] = String.valueOf(cursor.getInt(cursor.getColumnIndex("use_num")));
			}
			cursor.close();
		}
		database.close();
		return result;
	}
	// 插入一条数据
	@SuppressLint("SimpleDateFormat")
	public static void insertData(PrologueModel prologueModel) {
		SQLiteDatabase database = helper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("prologue_txt", prologueModel.getPrologueTxt());
		values.put("prologue_type", prologueModel.getPrologueType());
		values.put("prologue_state", prologueModel.getPrologueState());
		values.put("prologue_audio", prologueModel.getPrologueAudio());
		values.put("prologue_status", 1);
		values.put("create_time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
		database.insert("prologue_list", null, values);
		values.clear();
		database.close();
	}
	//更改启用状态
	@SuppressLint("Range")
	public static void updatePrologueStatus(int prologue_id, int prologue_status) {
		SQLiteDatabase database = helper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("prologue_status", prologue_status);
		database.update("prologue_list", values, "prologue_id = ?", new String[] {String.valueOf(prologue_id)});
		values.clear();
		database.close();
	}
	//更改开场白文字
	@SuppressLint("Range")
	public static void updateData(PrologueModel prologueModel) {
		SQLiteDatabase database = helper.getReadableDatabase();
		//旧音频和新音频不一样则删除
		deleteDifferentFile(database,prologueModel.getPrologueId(),prologueModel.getPrologueAudio());
		ContentValues values = new ContentValues();
		values.put("prologue_txt", prologueModel.getPrologueTxt());
		values.put("prologue_type", prologueModel.getPrologueType());
		values.put("prologue_state", prologueModel.getPrologueState());
		values.put("prologue_audio", prologueModel.getPrologueAudio());
		database.update("prologue_list", values, "prologue_id = ?", new String[] {String.valueOf(prologueModel.getPrologueId())});
		values.clear();
		database.close();
	}
	//仅更改使用次数
	@SuppressLint("Range")
	public static void updateUseNumData(List<PrologueModel> list) {
		SQLiteDatabase database = helper.getReadableDatabase();
		for (PrologueModel prologueModel : list) {
			database.execSQL("UPDATE prologue_list SET use_num = use_num + "+prologueModel.getUseNum()+" WHERE prologue_id = "+prologueModel.getPrologueId());
		}
		database.close();
	}
	//删除
	public static void deleteData(int prologue_id) {
		SQLiteDatabase database = helper.getReadableDatabase();
		//先获取音频路径并且删除文件
		deleteFile(database,prologue_id);
		database.execSQL("delete FROM prologue_list WHERE prologue_id = "+prologue_id);
		database.close();
	}

	//先获取音频路径并且删除文件
	@SuppressLint("Range")
	public static void deleteFile(SQLiteDatabase database,int prologue_id){
		Cursor cursorDel = database.rawQuery("select * from prologue_list where prologue_id="+prologue_id, null);
		if (cursorDel != null && cursorDel.getCount() > 0) {
			if (cursorDel.moveToNext()) {
				String answer_audio_old = cursorDel.getString(cursorDel.getColumnIndex("prologue_audio"));
				int answerType = cursorDel.getInt(cursorDel.getColumnIndex("prologue_type"));
				//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
				if(answerType == 1 || answerType == 2){
					//文件存在则删除
					File file = new File(answer_audio_old);
					if(file.exists()){
						file.delete();
					}
				}
			}
			cursorDel.close();
		}
	}
	//旧音频和新音频不一样则删除
	@SuppressLint("Range")
	public static void deleteDifferentFile(SQLiteDatabase database,int prologue_id,String new_answer_audio){
		Cursor cursorEdit = database.rawQuery("select * from prologue_list where prologue_id="+prologue_id, null);
		if (cursorEdit != null && cursorEdit.getCount() > 0) {
			if (cursorEdit.moveToNext()) {
				String answer_audio_old = cursorEdit.getString(cursorEdit.getColumnIndex("prologue_audio"));
				int answerType = cursorEdit.getInt(cursorEdit.getColumnIndex("prologue_type"));
				//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
				if(answerType == 1 || answerType == 2){
					//旧音频和新音频不一样
					if(!new_answer_audio.equals(answer_audio_old)){
						//文件存在则删除
						File file = new File(answer_audio_old);
						if(file.exists()){
							file.delete();
						}
					}
				}
			}
			cursorEdit.close();
		}
	}
}
